#!/usr/bin/env python
#coding=utf-8

from __future__ import division
import time
import sys
from define import BASE_DIR
sys.path.append(BASE_DIR)

from lib.abstract_cron import abstract_cron
from lib.func_ext import dateTypeInterval,str2sec,sec2str,ksort

#需要修改
class business_type_report_day(abstract_cron) :

    #需要修改
    #noLock = True   #是否生成脚本锁文件
    insertTable = 'sdb_statistics_business_type_report_day'

    def __init__(self) :
        #需要修改
        super(business_type_report_day,self).__init__(__file__)
        self.relDB = self.objCore.database('shopex_certi')

    def boot(self,timestamp={}) :
#        self.preBoot()
        
        #nodeType = self.getNodeType()
        bizData = self.getBizData(timestamp)
        relData = self.getRelData(timestamp)

        if bizData and relData :
            #比较2个字典的节点类型
            diffKey = set(bizData.keys()) - set(relData.keys())
            if diffKey :
                for key in diffKey :
                    relData[key] = bizData[key]

            for k,v in relData.iteritems() :
                v.update(bizData.get(k,{}))
                if not v.get('date') :
                    v['date'] = sec2str('%Y%m%d',time.time()-1*24*3600)
                if not v.get('calltime') :
                    v['calltime'] = int(time.time()-1*24*3600)
                #数据过滤
                self.filterItems(v)
        elif bizData :
            for k,v in bizData.iteritems() :
                #数据过滤
                self.filterItems(v)
        elif relData :
            for k,v in relData.iteritems() :
                #数据过滤
                self.filterItems(v)

        #批量插入
        self.doBatchExec()

        self.sufBoot()
        print 'ok'

    def getBizData(self,timestamp={}) :
        """""""""""""""""""""""""""""""""""""""
         # 获取商铺统计项数据
         #
         # @author   lazyfish <lazyfish.fan@gmail.com>
         # @param    
         # @return   dict
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        timeFilter = self.getTimeFilter(timestamp)
        bizData = {}
        for k,v in timeFilter.iteritems() :
            #总量和当日量区分
            if k == 'total' :
                cols  = ' `node_type`,SUM(`api_sum`) AS api_visit_num_total, '
                cols += ' SUM(`api_failnum`) AS api_fail_num_total, '
                cols += ' SUM(`num`) AS order_num_total,SUM(`amount_money`) AS order_amount_total, '
                cols += ' COUNT(`node_id`) AS biz_visit_num_total '
            else :
                cols  = ' `node_type`,`date`,`calltime`, '
                cols += ' SUM(`api_sum`) AS api_visit_num,SUM(`api_failnum`) AS api_fail_num, '
                cols += ' SUM(`num`) AS order_num,SUM(`amount_money`) AS order_amount, '
                cols += ' COUNT(`node_id`) AS biz_visit_num '
                
            sql  = ' SELECT '+cols
            sql += ' FROM sdb_statistics_business_report WHERE '+self.db._filter(v)
            sql += ' GROUP BY `node_type`'
            row = self.db.select(sql)
            if row :
                self.writeSql(sql,'succ','select')
                for v in row :
                    if k == 'day' :
                        self.figureRatio(v,'api_visit_num','api_fail_num','api_succ_ratio')
                    elif k == 'total' :
                        self.figureRatio(v,'api_visit_num_total','api_fail_num_total','api_succ_ratio_total')

                    #过滤node_type值为False的项
                    node_type = v.get('node_type')
                    if node_type :
                        if node_type not in bizData :
                            bizData[node_type] = {}
                        bizData[node_type].update(v)
            else :
                self.writeSql(sql,'fail','select')
        return bizData

    def getRelData(self,timestamp={}) :
        relData = {}

        yesterday = dateTypeInterval('yesterday')
        time_begin = str2sec(yesterday[0],'%Y-%m-%d')
        time_end = str2sec(yesterday[1],'%Y-%m-%d')+1*24*3600
        
        keyFilter = {}
        dayInterval = timestamp.get('rel_day')
        keyFilter['day'] = {'type':'between','value':(dayInterval and dayInterval or [time_begin,time_end])}
        totalInterval = timestamp.get('rel_total')
        keyFilter['total'] = {'type':'lt','value':(totalInterval and totalInterval or str2sec(sec2str('%Y%m%d'),'%Y%m%d'))}
    
        #接入矩阵商铺数
        bindShopFilter = {}
        for k,v in keyFilter.iteritems() :
            #总量和当日量 区分key
            bindShopKey = (k=='total') and 'bind_shop_total' or 'bind_shop'
            bindShopFilter['status'] = 'true'
            bindShopFilter['esb_service_status'] = 'true'
            bindShopFilter['addtime|'+v['type']] = v['value']
            where = self.relDB._filter(bindShopFilter)
            sql  = ' SELECT count(*) AS '+bindShopKey+' , `node_type` FROM '
            sql += ' ( '
            sql += ' SELECT `from_node_id` AS node_id, `from_node_type` AS node_type '
            sql += ' FROM `svc_node_relation` WHERE '+where
            sql += ' UNION '
            sql += ' SELECT `to_node_id` AS node_id, `to_node_type` AS node_type '
            sql += ' FROM `svc_node_relation` WHERE '+where
            sql += ' ORDER BY `node_type` '
            sql += ' ) AS tmp '
            sql += ' GROUP BY `node_type` '
            row = self.relDB.select(sql)
            if row :
                self.writeSql(sql,'succ','select')
                for v in row :
                    node_type = v.get('node_type')
                    if node_type :
                        if node_type not in relData :
                            relData[node_type] = {}
                        relData[node_type].update(v)
                        #relData[node_type]['date'] = sec2str('%Y%m%d',time.time()-1*24*3600)
                        #relData[node_type]['calltime'] = int(time.time()-1*24*3600)
            else :
                self.writeSql(sql,'fail','select')

        #已绑定数目
        bindFilter = {}
        for k,v in keyFilter.iteritems() :
            #总量和当日量 区分key
            bindKey = (k=='total') and 'rel_bind_num_total' or 'rel_bind_num'
            bindFilter['esb_service_status'] = 'true'
            bindFilter['status'] = 'true'
            bindFilter['addtime|'+v['type']] = v['value']
            sql  = ' SELECT COUNT(*) AS '+bindKey+',`from_node_type` AS node_type '
            #具体时间已经不重要,如果加上,计算总量得到的时间可能不是今天这个时间段的
            #sql += ' MAX(`addtime`) AS calltime, '
            #sql += ' FROM_UNIXTIME(MAX(`addtime`),"%Y%m%d") AS date '
            sql += ' FROM svc_node_relation WHERE '+self.relDB._filter(bindFilter)
            sql += ' GROUP BY `from_node_type`'
            row = self.relDB.select(sql)
            if row :
                self.writeSql(sql,'succ','select')
                for v in row :
                    node_type = v.get('node_type')
                    if node_type :
                        if node_type not in relData :
                            relData[node_type] = {}
                        relData[node_type].update(v)
            else :
                self.writeSql(sql,'fail','select')

        #已解绑数目
        unbindFilter = {}
        for k,v in keyFilter.iteritems() :
            #总量和当日量 区分key
            unbindKey = (k=='total') and 'rel_unbind_num_total' or 'rel_unbind_num'
            unbindFilter['status'] = 'del'
            unbindFilter['deltime|'+v['type']] = v['value']
            sql  = ' SELECT COUNT(*) AS '+unbindKey+',`from_node_type` AS node_type '
            #具体时间已经不重要,如果加上,计算总量得到的时间可能不是今天这个时间段的
            #sql += ' MAX(`deltime`) AS calltime,'
            #sql += ' FROM_UNIXTIME(MAX(`deltime`),"%Y%m%d") AS date '
            sql += ' FROM svc_node_relation WHERE '+self.relDB._filter(unbindFilter)
            sql += ' GROUP BY `from_node_type`'
            row = self.relDB.select(sql)
            if row :
                self.writeSql(sql,'succ','select')
                for v in row :
                    node_type = v.get('node_type')
                    if node_type :
                        if node_type not in relData :
                            relData[node_type] = {}
                        relData[node_type].update(v)
            else :
                self.writeSql(sql,'fail','select')
        return relData

    def getTimeFilter(self,timestamp={}) :
        keyFilter = {}
        date = timestamp.get('date')
        keyFilter['day'] = {'date':(date and date or int(sec2str('%Y%m%d',time.time()-1*24*3600)))}
        calltime = timestamp.get('calltime')
        keyFilter['total'] = {'calltime|lt':(calltime and calltime or str2sec(sec2str('%Y%m%d'),'%Y%m%d'))}
        return keyFilter

    def insertKeys(self) :
        return [
            'node_type','date','calltime',
            'bind_shop','bind_shop_total',
            'api_visit_num','api_visit_num_total',
            'api_fail_num','api_fail_num_total',
            'api_succ_ratio','api_succ_ratio_total',
            'order_num','order_num_total',
            'order_amount','order_amount_total',
            'rel_bind_num','rel_bind_num_total',
            'rel_unbind_num','rel_unbind_num_total',
            'biz_visit_num','biz_visit_num_total'
            ]


#需要修改
if __name__ == '__main__' :
    obj = business_type_report_day()
    obj.boot()
    #timestamp = {'calltime': 3, 'date': 20110405,'rel_day':[2, 4],'rel_total':5}
    #obj.boot(timestamp)
